<?php
class Controller_DataCenter_Sales extends Controller_Abstract
{
	function actionResendRate()
	{
		$this->_view['title'] = '重发率报表';
		$shop = Shop::getActiveShop();
		$this->_view['shop'] = $shop;
		if ( ! empty($_GET['is_search']) && ! empty($_GET['fieldname']))
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::resendRate($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['all_count'] = $result['all_count'];
				$this->_view['resend_count'] = $result['resend_count'];
				$this->_view['result'] = $result['result'];
			}
		}
	}
	
	function actionSalesPeriod()
	{
		$this->_view['title'] = '销售周期表';
		$product_categories = Product_Categories::getCategoriesTree();
		$this->_view['product_categories'] = json_encode($product_categories);
		$shop = Shop_User_Mapping::getTreeShopByPlatform();
		$this->_view['shop_group'] = $shop;
		$warehouse = Warehouse::getActiveWarehouse();
		$this->_view['warehouse'] = $warehouse;
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		if ($is_search)
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::salesPeriod($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['result'] = $result['result'];
				$this->_view['type'] = $result['type'];
			}
			else
			{
				return $this->_redirectMessage('查询失败', $result['message'], url('Datacenter::Sales/SalesPeriod'), $result['ack']);
			}
		}
	}
	
	function actionSalesPeriodByShop()
	{
		$this->_view['title'] = '销售周期表-依店铺';
		$shop = Shop_User_Mapping::getTreeShopByPlatform();
		$this->_view['shop_group'] = $shop;
		$warehouse = Warehouse::getActiveWarehouse();
		$this->_view['warehouse'] = $warehouse;
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		if ($is_search)
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::salesPeriodByShop($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['result'] = $result['result'];
			}
			else
			{
				return $this->_redirectMessage('查询失败', $result['message'], url('Datacenter::Sales/SalesPeriodByShop'), $result['ack']);
			}
		}
	}
	
	function actionSalesPeriodByCategory()
	{
		$this->_view['title'] = '销售周期表-依分类';
		$shop = Shop_User_Mapping::getTreeShopByPlatform();
		$this->_view['shop_group'] = $shop;
		$product_categories = Product_Categories::getCategoriesTree();
		$this->_view['product_categories'] = json_encode($product_categories);
		$warehouse = Warehouse::getActiveWarehouse();
		$this->_view['warehouse'] = $warehouse;
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		if ($is_search)
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::salesPeriodByCategory($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['result'] = $result['result'];
			}
			else
			{
				return $this->_redirectMessage('查询失败', $result['message'], url('Datacenter::Sales/SalesPeriodByCategory'), $result['ack']);
			}
		}
	}
	
	function actionSalesPeriodByShopAndCategory()
	{
		$this->_view['title'] = '销售周期表-依店铺|分类';
		$product_categories = Product_Categories::getCategoriesTree();
		$this->_view['product_categories'] = json_encode($product_categories);
		$shop = Shop_User_Mapping::getTreeShopByPlatform();
		$this->_view['shop_group'] = $shop;
		$warehouse = Warehouse::getActiveWarehouse();
		$this->_view['warehouse'] = $warehouse;
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		if ($is_search)
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::salesPeriodByShopAndCategory($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['result'] = $result['result'];
			}
			else
			{
				return $this->_redirectMessage('查询失败', $result['message'], url('Datacenter::Sales/SalesPeriodByShopAndCategory'), $result['ack']);
			}
		}
	}
	
	function actionRefundReport()
	{
		$this->_view['title'] = '退款统计报表';
		$shop = Shop::getActiveShop();
		$this->_view['shop'] = $shop;
		if ( ! empty($_GET['is_search']))
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::refund($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['refund_count'] = $result['refund_count'];
				$this->_view['reason'] = $result['reason'];
			}
		}
	}
	
	function actionSalesAnalysisByShopCategories()
	{
		$this->_view['title'] = '销售分析-依网站分类 ';//目前只需要做iwowcase的就可以了
		$shop = Shop::find('status=1 and platform=? and (name=? or name=?)', 'zencart', 'iwowcase.com', 'pashenn.com')->order('shop.sort desc')->asArray()->getAll();
		$shop_categories = OLM_Zencart_Categories::getZencartCategoriesToSelectFormat($shop);
		$this->_view['shop_categories'] = json_encode($shop_categories);
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		if ($is_search)
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::salesAnalysisByShopCategories($url_arr, $shop);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['result'] = $result['result'];
			}
		}
	}
	
	function actionTopSales()
	{
		$this->_view['title'] = '销售分析-依产品(排行榜)';
		$columns = array(
			'product_warehouse.weekly_sales, product_warehouse.monthly_sales, product_warehouse.sales_trend',
			'product_basic.name, product_basic.number'
		);
	
		//周销
		$week_sales = Product_Warehouse::find()
			->joinLeft('product_basic', '', 'product_basic.id=product_warehouse.product_basic_id')
			->setColumns($columns)->order('weekly_sales desc')->limit(0, 20)->asArray()->getAll();
		Helper_BSS_Normal::getProductImgUrl($week_sales);
		$this->_view['weekly_sales'] = $week_sales;
		
		//月销
		$month_sales = Product_Warehouse::find()
			->joinLeft('product_basic', '', 'product_basic.id=product_warehouse.product_basic_id')
			->setColumns($columns)->order('monthly_sales desc')->limit(0, 20)->asArray()->getAll();
		Helper_BSS_Normal::getProductImgUrl($month_sales);
		$this->_view['monthly_sales'] = $month_sales;
	}
	
	function actionSalesAnalysisByProductCategories()
	{
		$this->_view['title'] = '销售分析-依产品分类';
		if ( ! empty($_GET['is_search']))
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::salesAnalysisByProductCategories($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['categories'] = $result['categories'];
			}
		}
	}
	
	function actionSalesOrderHandleAnalysis()
	{
		$this->_view['title'] = '销售处理进度表';
		$shop = Shop::find('status=1 and platform=?', 'zencart')->order('shop.sort desc')->asArray()->getAll();
		$this->_view['shop'] = $shop;
		if ( ! empty($_GET['is_search']))
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::salesOrderHandleAnalysis($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['result'] = $result['result'];
				$this->_view['count'] = $result['count'];
			}
		}
	}
	
	function actionRepurchaseStatistics()
	{
		$this->_view['title'] = '重复购买统计表';
		$shop = Shop::find('status=1')->order('shop.sort desc')->asArray()->getAll();
		$this->_view['shop'] = $shop;
		$purchase_categories = Product_Categories::getCategoriesTree();
		$this->_view['purchase_categories'] = json_encode($purchase_categories);
		if ( ! empty($_GET['is_search']))
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::repurchaseStatistics($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['customer'] = $result['result'];
			}
		}
	}
	
	function actionProductSales()
	{
		$this->_view['title'] = '产品销量报表';
		$product_categories = Product_Categories::getCategoriesTree();
		$this->_view['product_categories'] = json_encode($product_categories);
		$shop = Shop::getActiveShop();
		$shop_group = array();
		foreach ($shop as $s)
		{
			$shop_group[$s['platform']][] = $s;
		}
		$this->_view['shop_group'] = $shop_group;
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		if ($is_search)
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::productSales($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['product'] = $result['result'];
			}
		}
	}
	
	function actionProductSalesByShop()
	{
		$this->_view['title'] = '单品销售-按店铺';
		$this->_view['warehouse'] = Warehouse::find()->asArray()->getAll();
		if (isset($_GET['is_search']) && $_GET['is_search'] == 1)
		{
			if (! empty($_GET['product_number']))
			{
				$condition = Helper_BSS_Normal::buildCondition();
				$result = OLM_Report::productSalesByShop($condition);
				$this->_view['product_sales'] = $result['data'];
			}
		}
	}
	
	function actionPriceMonitor()
	{
		$this->_view['title'] = '售价监控表';
		$shop = Shop::getActiveShop();
		$shop_group = array();
		foreach ($shop as $s)
		{
			$shop_group[$s['platform']][] = $s;
		}
		$this->_view['shop_group'] = $shop_group;
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		if ($is_search)
		{
			$cp = isset($_GET['cp']) ? $_GET['cp'] : 1;
			$ps = isset($_GET['ps']) ? $_GET['ps'] : 20;
			$url_arr = Helper_BSS_Normal::buildCondition();
			$url_arr['is_price_monitor'] = 1;
			$url_arr['trash_status'] = Q::ini('custom_flag/sales_order_trash_status/normal/value');
	 		$result = Sales_Order_Item::search($url_arr, $cp, $ps);
			if ($result['ack'] == SUCCESS)
			{
				$page = new Helper_BSS_Page($result['pagination'], url('DataCenter::Sales/PriceMonitor', $url_arr));
				$this->_view['page'] = $page->getPage();
				$this->_view['item'] = $result['data'];
			}
		}
	}
	
	function actionUnshippedOrderStatis()
	{
		$this->_view['title'] = '未发货订单统计表';
		$url_arr = Helper_BSS_Normal::buildCondition();
		$this->_view['url_arr_json'] = json_encode($url_arr);
		$shop = Shop::getActiveShop();
		$shop_group = array();
		foreach ($shop as $s)
		{
			$shop_group[$s['platform']][] = $s;
		}
		$this->_view['shop_group'] = $shop_group;
		if (isset($_GET['end_time']) && $_GET['end_time'])
		{
			$result = OLM_Report::unshippedOrderStatis($_GET);
			$this->_view['unshipped_order_statis'] = $result['result'];
		}
	}
	
	function actionUnshippedOrderStatisDownload()
	{
		$this->_view['title'] = '未发货订单统计表下载';
		if (isset($_GET['url_arr_json']))
		{
			$condition = json_decode($_GET['url_arr_json'], true);
			$result = OLM_Report::unshippedOrderStatis($condition);
			include Q::ini('custom_system/lib_dir').'PHPExcel.php';
			$phpexcel = new PHPExcel();
			$phpexcel->getProperties()->setCreator(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
			$phpexcel->getProperties()->setLastModifiedBy(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
			$phpexcel->getProperties()->setTitle("未发货订单统计报表");
			$phpexcel->getProperties()->setSubject("未发货订单统计报表");
			$phpexcel->getProperties()->setDescription("未发货订单统计报表-运营支撑系统");
			$phpexcel->getProperties()->setKeywords("未发货订单统计报表");
			$phpexcel->getProperties()->setCategory("未发货订单统计报表");
			$phpexcel->setActiveSheetIndex(0);
			$phpexcel->getActiveSheet()->setTitle("未发货订单统计报表");
			$phpexcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
			$phpexcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
			$phpexcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
			$phpexcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
			$phpexcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
			$phpexcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
			$phpexcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
			$phpexcel->getActiveSheet()->getColumnDimension('H')->setWidth(25);
			$phpexcel->getActiveSheet()->setCellValue('A1', "未处理时间");
			$phpexcel->getActiveSheet()->setCellValue('B1', "对应日期");
			$phpexcel->getActiveSheet()->setCellValue('C1', "订单总数量");
			$phpexcel->getActiveSheet()->setCellValue('D1', "未处理订单总数量");
			$phpexcel->getActiveSheet()->setCellValue('E1', "未处理订单数比例");
			$phpexcel->getActiveSheet()->setCellValue('F1', "订单总金额");
			$phpexcel->getActiveSheet()->setCellValue('G1', "未处理订单金额");
			$phpexcel->getActiveSheet()->setCellValue('H1', "未处理订单金额比例");
			$phpexcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
			$phpexcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
			$phpexcel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
			$phpexcel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);
			$phpexcel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);
			$phpexcel->getActiveSheet()->getStyle('F1')->getFont()->setBold(true);
			$phpexcel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true);
			$phpexcel->getActiveSheet()->getStyle('H1')->getFont()->setBold(true);
			$i = 2;
			$total_count = $total_amount = $total_unshipped_count = $total_unshipped_amount = 0;
			foreach ($result['result'] as $k => $r)
			{
				$total_count += $r['count'];
				$total_amount += $r['amount'];
				$total_unshipped_count += $r['unshipped_count'];
				$total_unshipped_amount += $r['unshipped_amount'];
				$phpexcel->getActiveSheet()->setCellValue('A'.$i, $k);
				$phpexcel->getActiveSheet()->setCellValue('B'.$i, $r['date']);
				$phpexcel->getActiveSheet()->setCellValue('C'.$i, $r['count']);
				$phpexcel->getActiveSheet()->setCellValue('D'.$i, $r['unshipped_count']);
				$phpexcel->getActiveSheet()->setCellValue('E'.$i, number_format($r['unshipped_count_PCT'], 2) . '%');
				$phpexcel->getActiveSheet()->setCellValue('F'.$i, number_format($r['amount'], 2));
				$phpexcel->getActiveSheet()->setCellValue('G'.$i, number_format($r['unshipped_amount'], 2));
				$phpexcel->getActiveSheet()->setCellValue('H'.$i, number_format($r['unshipped_amount_PCT'], 2) . '%');
				$i++;
			}
			$phpexcel->getActiveSheet()->setCellValue('A'.$i, '合计');
			$phpexcel->getActiveSheet()->setCellValue('B'.$i, '—');
			$phpexcel->getActiveSheet()->setCellValue('C'.$i, $total_count);
			$phpexcel->getActiveSheet()->setCellValue('D'.$i, $total_unshipped_count);
			$phpexcel->getActiveSheet()->setCellValue('E'.$i, number_format($total_unshipped_count/$total_count*100, 2) . '%');
			$phpexcel->getActiveSheet()->setCellValue('F'.$i, number_format($total_amount, 2));
			$phpexcel->getActiveSheet()->setCellValue('G'.$i, number_format($total_unshipped_amount, 2));
			$phpexcel->getActiveSheet()->setCellValue('H'.$i, number_format($total_unshipped_amount/$total_amount*100, 2) . '%');
			$outputFileName = "UnshippedOrderStatis.xls";
			$objWriter = new PHPExcel_Writer_Excel5($phpexcel);
			header("Content-Type: application/vnd.ms-execl");
			header("Content-Type: application/octet-stream");
			header("Content-Type: application/download");
			header('Content-Disposition:inline;filename="'.$outputFileName.'"');
			header("Content-Transfer-Encoding: binary");
			header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
			header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
			header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
			header("Pragma: no-cache");
			$objWriter->save('php://output');
			exit;
		}
	}
	
	function actionStockAnalysis()
	{
		$this->_view['title'] = '库存分析表';
		$purchase_categories = Product_Categories::getCategoriesTree();
		$this->_view['purchase_categories'] = json_encode($purchase_categories);
		$user = Purchase_Order::getUser();
		$this->_view['user'] = $user['data'];
		$warehouse = Warehouse::find()->asArray()->getAll();
		$this->_view['warehouse'] = $warehouse;
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		if ($is_search)
		{
			$cp = isset($_GET['cp']) ? $_GET['cp'] : 0;
			$ps = isset($_GET['ps']) ? $_GET['ps'] : 20;
			$url_arr = Helper_BSS_Normal::buildCondition();
			$this->_view['url_arr_json'] = urlencode(json_encode($url_arr));
			if (isset($url_arr['sort_by']) && isset($url_arr['sort_order']))
			{
				$order_by = $url_arr['sort_by'] . ' ' . $url_arr['sort_order'];
				$result = Product_Warehouse::search($url_arr, $cp, $ps, $order_by);
			}
			else
			{
				$result = Product_Warehouse::search($url_arr, $cp, $ps);
			}
			if ($result['ack'] == SUCCESS)
			{
				$page = new Helper_BSS_Page($result['pagination'], url('DataCenter::Sales/StockAnalysis', $url_arr));
				$this->_view['page'] = $page->getPage();
				Helper_BSS_Normal::getProductImgUrl($result['data']);
				$this->_view['product'] = $result['data'];
				unset($url_arr['sort_by']);
				unset($url_arr['sort_order']);
				$this->_view['url'] = url('DataCenter::Sales/StockAnalysis', $url_arr);
				if (isset($url_arr['pass_date']) && $url_arr['pass_date'])
				{
					$this->_view['dates'] = explode(',', $url_arr['pass_date']);
				}
			}
		}
	}
	
	function actionCustomizeDispalyStockAnalysis()
	{
		$this->_view['title'] = '自定义显示列';
		if (isset($_POST['fieldname']) && ($_POST['fieldname'] !== $_SESSION[Q::ini('custom_userdata_session_key')]['customize_sales_stock_analysis']))
		{
			session_start();
			$_SESSION[Q::ini('custom_userdata_session_key')]['customize_sales_stock_analysis'] = $_POST['fieldname'];
			QDB::getConn()->execute('update user set custom_session_data = \'' . json_encode($_SESSION[Q::ini('custom_userdata_session_key')], JSON_UNESCAPED_UNICODE) . '\' where id = ' . CURRENT_USER_ID);
			session_write_close();
		}
		return $this->_redirect($_SERVER['HTTP_REFERER']);
	}
	
	function actionUpdateProductSalesStatusSingle()
	{
		$this->_view['title'] = '更新单个产品销售状态';
		$temp = explode('_', $_REQUEST['data']);
		$product_warehouse_id = $temp[0];
		$sales_status = $temp[1];
		Product_Warehouse::updateSalesStatus($product_warehouse_id, 0, 0, $sales_status, CURRENT_USER_ID);
		$ret = array(
				'ack' => 'success',
				'status_html' => "<font color='#".Helper_BSS_Normal::getIniOne('product_sales_status', 'value', $sales_status, 'color')."'>".Helper_BSS_Normal::getIniOne('product_sales_status', 'value', $sales_status, 'name')."</font>"
		);
		echo json_encode($ret);
		exit;
	}
	
	function actionUpdateProductSalesStatusAllArtificial()
	{
		$this->_view['title'] = '更新产品状态';
		OLM_Cron::UpdateProductStatus();
	}
	
	function actionProductNotes()
	{
		$this->_view['title'] = '产品备注';
		$product = Product_basic::find('product_basic.id = ?', $_GET['id'])->asArray()->getOne();
		$this->_view['notes'] = $product['notes'];
	}
	
	function actionStockAnalysisDownload()
	{
		$this->_view['title'] = '库存分析表下载';
		set_time_limit(300);
		$url_arr = json_decode($_REQUEST['url_arr_json'], true);
		if (isset($url_arr['is_search']))
		{
			$result = Product_Warehouse::search($url_arr, 0, 0);
			$data = '';
			foreach ($_SESSION[Q::ini('custom_userdata_session_key')]['customize_sales_stock_analysis'] as $s)
			{
				$data .= "$s\t";
			}
			$data .= "\n";
			foreach ($result['data'] as $key => $r)
			{
				$r['purchase_status'] = Helper_BSS_Normal::getIniOne('product_purchase_status', 'value', $r['purchase_status'], 'name');
				$r['sales_status'] = Helper_BSS_Normal::getIniOne('product_sales_status', 'value', $r['sales_status'], 'name');
				$r['notes'] = str_replace(array("\n", "\r", "\r\n", "\t"), ' ', $r['notes']);
				$r['product_basic_name'] = str_replace(array("\n", "\r", "\r\n", "\t"), ' ', $r['product_basic_name']);
				$r['purchase_note'] = str_replace(array("\n", "\r", "\r\n", "\t"), ' ', $r['purchase_note']);
				foreach ($_SESSION[Q::ini('custom_userdata_session_key')]['customize_sales_stock_analysis'] as $k => $v)
				{
					$data .= "{$r[$k]}\t";
				}
				$data .= "\n";
			}
			$data = iconv("UTF-8","UTF-16LE",$data);
			$data = "\xFF\xFE".$data;
			$file_name = 'inventory-sales-' . date('YmdHis', CURRENT_TIMESTAMP) . '-' . CURRENT_USER_NAME . ".xls";
			$file = file_put_contents(Q::ini('custom_system/export_dir') . $file_name, $data);
			echo json_encode(array(
					'ack' => 'success',
					'url' => Q::ini('custom_system/export_url') . $file_name
			));
			exit;
		}
	}
	
	function actionSummaryClearance()
	{
		$this->_view['title'] = '清仓汇总表';
		$is_search = isset($_GET['is_search']) ? $_GET['is_search'] : '';
		if ($is_search)
		{
			$cp = isset($_GET['cp']) ? $_GET['cp'] : 0;
			$ps = isset($_GET['ps']) ? $_GET['ps'] : 20;
			$url_arr = Helper_BSS_Normal::buildCondition();
			$where = '1';
			if (! empty($url_arr['begin_time']))
			{
				$where .= ' and clearance_summary_log.update_time>=\'' . $url_arr['begin_time'] . '\'';
			}
			if (! empty($url_arr['end_time']))
			{
				$where .= ' and clearance_summary_log.update_time<\'' . $url_arr['end_time'] . ' 23:59:59\'';
			}
			$clearance = QDB::getConn()->execute('select * from clearance_summary_log where ' . $where)->fetchAll();
			$data = array();
			foreach ($clearance as $c)
			{
				if (! in_array($c['update_time'], $data))
				{
					$data[$c['update_time']][$c['status']] = array('number_quantity' => $c['number_quantity'], 'total_actual_quantity' => $c['total_actual_quantity'], 'total_value' => $c['total_value']);
				}
			}
			$this->_view['data'] = $data;
		}
	}
	
	function actionSalesReport()
	{
		set_time_limit(0);
		$this->_view['title'] = '销售统计表';
		$product_categories = Product_Categories::getCategoriesTree();
		$this->_view['product_categories'] = json_encode($product_categories);
		if (isset($_GET['is_search']) && $_GET['is_search'] == 1)
		{
			$sales_where = 'sales_order.trash_status=' . Q::ini('custom_flag/sales_order_trash_status/normal/value') . ' and (sales_order.handle_status=' . Q::ini('custom_flag/sales_order_handle_status/shipped/value') . ' or sales_order.handle_status=' . Q::ini('custom_flag/sales_order_handle_status/return/value') . ') and sales_order.shipping_status=' . Q::ini('custom_flag/sales_order_shipping_status/shipped/value');
			if ( ! empty($_GET['begin_shipped_time']))
			{
				$sales_where .= ' and sales_order.shipped_time>=\'' . $_GET['begin_shipped_time'] . '\'';
			}
			if ( ! empty($_GET['end_shipped_time']))
			{
				$sales_where .= ' and sales_order.shipped_time<=\'' . $_GET['end_shipped_time'] . ' 23:59:59\'';
			}
			if ( ! empty($_GET['product_categories_id']))
			{
				$categories_ids = Product_Categories::getOffspringById($_GET['product_categories_id']);
				array_push($categories_ids, $_GET['product_categories_id']);
				$sales_where .= ' and product_basic.product_categories_id in (' . implode(',', $categories_ids) . ')';
			}
			$shop = Shop_User_Mapping::getShopIdByUserId();
			$sales_where .= ' and shop.id in (\'' . implode('\',\'', $shop) . '\')';
			$sales_report = QDB::getConn()->execute("select shop.id as shop_id, shop.name as shop_name, sum(sales_order_item.cost_price*sales_order_item.quantity) as product_amount, sum(sales_order_item.sales_price*sales_order.currency_rate*sales_order_item.quantity) as sales_amount from sales_order_item left join sales_order on sales_order.id=sales_order_item.sales_order_id left join product_basic on product_basic.id=sales_order_item.product_basic_id left join shop on shop.id=sales_order.shop_id where {$sales_where} group by shop.id")->fetchAll();
			$total_product_amount = $total_sales_amount = 0;
			foreach ($sales_report as $sr)
			{
				$total_product_amount += $sr['product_amount'];
				$total_sales_amount += $sr['sales_amount'];
			}
			$this->_view['total_product_amount'] = $total_product_amount;
			$this->_view['total_sales_amount'] = $total_sales_amount;
			$this->_view['report'] = $sales_report;
		}
	}

	function actionUnsalableProduct()
	{
		set_time_limit(0);
		$this->_view['title'] = '滞销产品表';
		$product_categories = Product_Categories::getCategoriesTree();
		$this->_view['product_categories'] = json_encode($product_categories);
		$unsalable_days = ! empty($_GET['unsalable_days']) ? $_GET['unsalable_days'] : 30;
		$no_sku = ! empty($_GET['no_sku']) ? Q::normalize($_GET['no_sku'], ',') : array();
		if ( ! empty($_GET['node_time']) && ! empty($_GET['product_categories_id']))
		{
			$default_warehouse_id = Warehouse::getDefaultWarehouseId();
			$node_time = date('Y-m-01 00:00:00', strtotime($_GET['node_time']));
			$unsalable_begin = date('Y-m-d 00:00:00', strtotime($node_time) - $unsalable_days*3600*24);
			$unsalable_end = date('Y-m-d 23:59:59', strtotime($node_time) - 3600*24);
			$last_month_begin = date('Y-m-01 00:00:00', strtotime($node_time) - 3600*24);
			$current_month_end = date('Y-m-t 23:59:59', strtotime($node_time));
			$product_categories_ids = Q::normalize($_GET['product_categories_id'], ',');
			$no_sku_ids = QDB::getConn()->execute('select id from product_basic where number in (\'' . implode('\',\'', $no_sku) . '\')')->fetchCol();
			$result = $tmp = array();
			foreach ($product_categories_ids as $id)
			{
				$cids = Product_Categories::getOffspringById($id);
				array_push($cids, $id);
				
				//查找符合滞销规则产品ids
				$where = 'product_basic.create_time>=\'2012-01-01\' and product_basic.create_time<\'' . $unsalable_begin . '\' and inventory_log.remaining_quantity>0 and product_basic.product_categories_id in (\'' . implode('\',\'', $cids) . '\') and inventory_log.warehouse_id=' . $default_warehouse_id;
				$product_ids = QDB::getConn()->execute('select distinct(product_basic.id) from product_basic left join inventory_log on inventory_log.product_basic_id=product_basic.id where ' . $where)->fetchCol();
				$sales_out_product_ids = QDB::getConn()->execute('select distinct(inventory_log.product_basic_id) from inventory_log where inventory_log.time>=\'' . $unsalable_begin . '\' and inventory_log.time<=\'' . $unsalable_end . '\' and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/sales/value'))->fetchCol();
				$before_clearance_unsalabled_product_ids = array_diff($product_ids, $sales_out_product_ids, $no_sku_ids);
				//清仓前滞销产品规则条件
				$columns1 = 'product_basic.number, product_basic.create_time, product_basic.name, product_warehouse.sales_status, product_warehouse.purchase_status, sum(inventory_log.init_quantity) as init_quantity, sum(inventory_log.init_quantity*inventory_log.cost_price) as init_amount, sum(inventory_log.in_quantity) as in_quantity, sum(inventory_log.in_quantity*inventory_log.cost_price) as in_amount, sum(inventory_log.out_quantity) as out_quantity, sum(inventory_log.out_quantity*inventory_log.cost_price) as out_amount';
				$where1 = 'inventory_log.time<\'' . $node_time . '\' and inventory_log.warehouse_id=' . $default_warehouse_id;
				
				//清仓后滞销产品规则条件
				$columns2 = 'product_basic_id, sum(in_quantity) as in_quantity, sum(in_quantity*cost_price) as in_amount, sum(out_quantity) as out_quantity, sum(out_quantity*cost_price) as out_amount';;
				$where2 = 'time>=\'' . $node_time . '\' and time<=\'' . $current_month_end . '\' and warehouse_id=' . $default_warehouse_id;;
				
				//滞销产品节点所处当月销量规则条件
				$where3 = 'sales_order.warehouse_id=' . $default_warehouse_id . ' and sales_order.trash_status=' . Q::ini('custom_flag/sales_order_trash_status/normal/value') . ' and sales_order.shipping_status=' . Q::ini('custom_flag/sales_order_shipping_status/shipped/value') . ' and sales_order.shipped_time>=\'' . $last_month_begin . '\' and sales_order.shipped_time<\'' . $node_time . '\'';
				$columns3 = 'shop.id as shop_id, shop.name as shop_name, sum(sales_order_item.cost_price*sales_order_item.quantity) as product_amount, sum(sales_order_item.sales_price*sales_order.currency_rate*sales_order_item.quantity) as sales_amount';
				
				$no_clearance_amount = 0;
				$result[$id]['unsalabled_product'] = array();
				foreach ($before_clearance_unsalabled_product_ids as $pid)
				{
					//清仓前数量和金额统计
					$before = QDB::getConn()->execute('select ' . $columns1 . ' from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id left join product_warehouse on product_warehouse.product_basic_id=inventory_log.product_basic_id and product_warehouse.warehouse_id=inventory_log.warehouse_id where ' . $where1 . ' and inventory_log.product_basic_id=' . $pid)->fetchAll();
					$result[$id]['unsalabled_product'][$pid] = $before[0];
					$result[$id]['unsalabled_product'][$pid]['before_clearance_quantity'] = $before[0]['init_quantity'] + $before[0]['in_quantity'] - $before[0]['out_quantity'];
					$result[$id]['unsalabled_product'][$pid]['before_clearance_amount'] = $before[0]['init_amount'] + $before[0]['in_amount'] - $before[0]['out_amount'];

					//清仓后数量和金额统计
					$after = QDB::getConn()->execute('select ' . $columns2 . ' from inventory_log where ' . $where2 . ' and product_basic_id =' . $pid)->fetchAll();
					$result[$id]['unsalabled_product'][$pid]['after_clearance_quantity'] = $result[$id]['unsalabled_product'][$pid]['before_clearance_quantity'] + $after[0]['in_quantity'] - $after[0]['out_quantity'];
					$result[$id]['unsalabled_product'][$pid]['after_clearance_amount'] = $result[$id]['unsalabled_product'][$pid]['before_clearance_amount'] + $after[0]['in_amount'] - $after[0]['out_amount'];
					if ($after[0]['out_amount'])
					{
						$no_clearance_amount += $result[$id]['unsalabled_product'][$pid]['before_clearance_amount']; 
					}
					
					//滞销品当前月份销量统计
					$current_month_sales = QDB::getConn()->execute('select ' . $columns3 . ' from sales_order_item left join sales_order on sales_order.id=sales_order_item.sales_order_id left join shop on shop.id=sales_order.shop_id where ' . $where3 . ' and sales_order_item.product_basic_id='. $pid)->fetchAll();
					if (in_array($current_month_sales[0]['shop_id'], $tmp))
					{
						$tmp[$current_month_sales[0]['shop_id']]['product_amount'] += $current_month_sales[0]['product_amount'];
						$tmp[$current_month_sales[0]['shop_id']]['sales_amount'] += $current_month_sales[0]['sales_amount'];
					}
					else
					{
						$tmp[$current_month_sales[0]['shop_id']] = $current_month_sales[0];
					}
				}
				$result[$id]['current_month_sales'] = $tmp;
				$result[$id]['clearance'] = $no_clearance_amount;
				$where = 'sales_order.warehouse_id=' . $default_warehouse_id . ' and sales_order.trash_status=' . Q::ini('custom_flag/sales_order_trash_status/normal/value') . ' and sales_order.shipping_status=' . Q::ini('custom_flag/sales_order_shipping_status/shipped/value') . ' and sales_order.shipped_time>=\'' . $last_month_begin . '\' and sales_order.shipped_time<\'' . $node_time . '\' and product_basic.product_categories_id in (\'' . implode( '\',\'', $cids) . '\')';
				$result[$id]['last_month_sales'] = QDB::getConn()->execute('select ' . $columns3 . ' from sales_order_item left join sales_order on sales_order.id=sales_order_item.sales_order_id left join product_basic on product_basic.id=sales_order_item.product_basic_id left join shop on shop.id=sales_order.shop_id where ' . $where . ' group by shop.id')->fetchAll();
			}
			
			include Q::ini('custom_system/lib_dir') . 'PHPExcel.php';
			$phpexcel = new PHPExcel();
			$phpexcel->getProperties()->setCreator(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
			$phpexcel->getProperties()->setLastModifiedBy(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
			$phpexcel->getProperties()->setTitle('滞销产品信息');
			$phpexcel->getProperties()->setSubject('滞销产品信息');
			$phpexcel->getProperties()->setDescription('滞销产品信息-运营系统支撑系统');
			$phpexcel->getProperties()->setKeywords('滞销产品信息');
			$phpexcel->getProperties()->setCategory('滞销产品信息');
			$index = 0;
			foreach ($result as $key => $r)
			{
				if ($index != 0)
				{
					$phpexcel->createSheet();
				}
				$phpexcel->setActiveSheetIndex($index);
				$product_categories = Product_Categories::find('id=?', $key)->setColumns('name')->asArray()->getOne();
				$phpexcel->getActiveSheet()->setTitle($product_categories['name']);
				/* 滞销产品表 */
				$phpexcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
				$phpexcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
				$phpexcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
				$phpexcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
				$phpexcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
				$phpexcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
				$phpexcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
				$phpexcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);
				$phpexcel->getActiveSheet()->getColumnDimension('I')->setWidth(12);
				$phpexcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
				$phpexcel->getActiveSheet()->getColumnDimension('K')->setWidth(14);
				$phpexcel->getActiveSheet()->getColumnDimension('L')->setWidth(14);
				$phpexcel->getActiveSheet()->getColumnDimension('M')->setWidth(14);
				$phpexcel->getActiveSheet()->getColumnDimension('N')->setWidth(14);
				$phpexcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
				$phpexcel->getActiveSheet()->mergeCells("A1:I1");
				$phpexcel->getActiveSheet()->getStyle("A1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				$phpexcel->getActiveSheet()->getStyle("A1")->getFont()->setBold(true);
				$phpexcel->getActiveSheet()->setCellValue('A1', '滞销天数为 ' . $unsalable_days . ' 的滞销产品信息');
				$i = 2;
				$phpexcel->getActiveSheet()->setCellValue('A' . $i, '产品编号');
				$phpexcel->getActiveSheet()->setCellValue('B' . $i, '产品编号生成时间');
				$phpexcel->getActiveSheet()->setCellValue('C' . $i, '产品名称');
				$phpexcel->getActiveSheet()->setCellValue('D' . $i, '清仓前数量');
				$phpexcel->getActiveSheet()->setCellValue('E' . $i, '清仓前金额');
				$phpexcel->getActiveSheet()->setCellValue('F' . $i, '清仓后数量');
				$phpexcel->getActiveSheet()->setCellValue('G' . $i, '清仓后金额');
				$phpexcel->getActiveSheet()->setCellValue('H' . $i, '销售状态');
				$phpexcel->getActiveSheet()->setCellValue('I' . $i, '采购状态');
				$i++;
				$total_before_clearance_quantity = $total_before_clearance_amount = $total_after_clearance_quantity = $total_after_clearance_amount = 0;
				foreach ($r['unsalabled_product'] as $ru)
				{
					$total_before_clearance_quantity += $ru['before_clearance_quantity'];
					$total_before_clearance_amount += $ru['before_clearance_amount'];
					$total_after_clearance_quantity += $ru['after_clearance_quantity'];
					$total_after_clearance_amount += $ru['after_clearance_amount'];
					$phpexcel->getActiveSheet()->getCell('A' . $i)->setValueExplicit($ru['number'], PHPExcel_Cell_DataType::TYPE_STRING);
					$phpexcel->getActiveSheet()->setCellValue('B' . $i, $ru['create_time']);
					$phpexcel->getActiveSheet()->setCellValue('C' . $i, $ru['name']);
					$phpexcel->getActiveSheet()->setCellValue('D' . $i, $ru['before_clearance_quantity']);
					$phpexcel->getActiveSheet()->setCellValue('E' . $i, sprintf('%.2f', $ru['before_clearance_amount']));
					$phpexcel->getActiveSheet()->setCellValue('F' . $i, $ru['after_clearance_quantity']);
					$phpexcel->getActiveSheet()->setCellValue('G' . $i, sprintf('%.2f', $ru['after_clearance_amount']));
					$phpexcel->getActiveSheet()->setCellValue('H' . $i, Helper_BSS_Normal::getIniOne('product_sales_status', 'value', $ru['sales_status'], 'name'));
					$phpexcel->getActiveSheet()->setCellValue('I' . $i, Helper_BSS_Normal::getIniOne('product_purchase_status', 'value', $ru['purchase_status'], 'name'));
					$i++;
				}
				$phpexcel->getActiveSheet()->setCellValue('A' . $i, '合计');
				$phpexcel->getActiveSheet()->setCellValue('B' . $i, '');
				$phpexcel->getActiveSheet()->setCellValue('C' . $i, '');
				$phpexcel->getActiveSheet()->setCellValue('D' . $i, $total_before_clearance_quantity);
				$phpexcel->getActiveSheet()->setCellValue('E' . $i, sprintf('%.2f', $total_before_clearance_amount));
				$phpexcel->getActiveSheet()->setCellValue('F' . $i, $total_after_clearance_quantity);
				$phpexcel->getActiveSheet()->setCellValue('G' . $i, sprintf('%.2f', $total_after_clearance_amount));
				$i += 3;
				/* 该产品分类时间结点上月各店铺销售情况 */
				$phpexcel->getActiveSheet()->mergeCells("J{$i}:N{$i}");
				$phpexcel->getActiveSheet()->getStyle("J{$i}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				$phpexcel->getActiveSheet()->getStyle("J{$i}")->getFont()->setBold(true);
				$phpexcel->getActiveSheet()->setCellValue('J' . $i, '该产品分类上月各店铺销售情况');
				$i++;
				$phpexcel->getActiveSheet()->setCellValue('J' . $i, '店铺名');
				$phpexcel->getActiveSheet()->setCellValue('K' . $i, '产品成本');
				$phpexcel->getActiveSheet()->setCellValue('L' . $i, '产品成本占率');
				$phpexcel->getActiveSheet()->setCellValue('M' . $i, '销售金额');
				$phpexcel->getActiveSheet()->setCellValue('N' . $i, '销售金额占率');
				$i++;
				$total_product_amount = $total_sales_amount = 0;
				foreach ($r['last_month_sales'] as $rl)
				{
					$total_product_amount += $rl['product_amount'];
					$total_sales_amount += $rl['sales_amount'];
				}
				$shop = array();
				foreach ($r['last_month_sales'] as $k => $rl)
				{ 
					$product_amount_rate = empty($total_product_amount) ? '0' : sprintf('%.4f', $rl['product_amount'] / $total_product_amount);
					$sales_amount_rate = empty($total_sales_amount) ? '0' : sprintf('%.4f', $rl['sales_amount'] / $total_sales_amount);
					$shop[$rl['shop_id']]['shop_name'] = $rl['shop_name'];
					$shop[$rl['shop_id']]['out_rate'] = $product_amount_rate;
					$phpexcel->getActiveSheet()->setCellValue('J' . $i, $rl['shop_name']);
					$phpexcel->getActiveSheet()->setCellValue('K' . $i, sprintf('%.2f', $rl['product_amount']));
					$phpexcel->getActiveSheet()->setCellValue('L' . $i, sprintf('%.2f', $product_amount_rate * 100) . '%');
					$phpexcel->getActiveSheet()->setCellValue('M' . $i, sprintf('%.2f', $rl['sales_amount']));
					$phpexcel->getActiveSheet()->setCellValue('N' . $i, sprintf('%.2f', $sales_amount_rate * 100) . '%');
					$i++;
				}
				$phpexcel->getActiveSheet()->setCellValue('J' . $i, '合计');
				$phpexcel->getActiveSheet()->setCellValue('K' . $i, $total_product_amount);
				$phpexcel->getActiveSheet()->setCellValue('L' . $i, '');
				$phpexcel->getActiveSheet()->setCellValue('M' . $i, $total_sales_amount);
				$phpexcel->getActiveSheet()->setCellValue('N' . $i, '');
				$i += 3;
				/* 滞销产品时间结点所在当月的销售情况 */
				$phpexcel->getActiveSheet()->mergeCells("J{$i}:O{$i}");
				$phpexcel->getActiveSheet()->getStyle("J{$i}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				$phpexcel->getActiveSheet()->getStyle("J{$i}")->getFont()->setBold(true);
				$phpexcel->getActiveSheet()->setCellValue('J' . $i, '滞销产品节点时间所处当月的销售情况');
				$i++;
				$phpexcel->getActiveSheet()->setCellValue('J' . $i, '店铺名');
				$phpexcel->getActiveSheet()->setCellValue('K' . $i, '产品成本');
				$phpexcel->getActiveSheet()->setCellValue('L' . $i, '产品成本占率');
				$phpexcel->getActiveSheet()->setCellValue('M' . $i, '销售金额');
				$phpexcel->getActiveSheet()->setCellValue('N' . $i, '销售金额占率');
				$phpexcel->getActiveSheet()->setCellValue('O' . $i, '剩余分摊金额');
				$i++;
				$total_product_amount = $total_sales_amount = 0;
				$current_shop = array();
				foreach ($r['current_month_sales'] as $rc)
				{
					$current_shop[$rc['shop_id']] = $rc;
					$total_product_amount += $rc['product_amount'];
					$total_sales_amount += $rc['sales_amount'];
				}
				foreach ($shop as $k => $s)
				{
					if (isset($current_shop[$k]))
					{
						$phpexcel->getActiveSheet()->setCellValue('J' . $i, $s['shop_name']);
						$phpexcel->getActiveSheet()->setCellValue('K' . $i, sprintf('%.2f', $current_shop[$k]['product_amount']));
						$phpexcel->getActiveSheet()->setCellValue('L' . $i, empty($total_product_amount) ? '0.00%' : (sprintf('%.2f', $current_shop[$k]['product_amount'] / $total_product_amount * 100). '%'));
						$phpexcel->getActiveSheet()->setCellValue('M' . $i, sprintf('%.2f', $current_shop[$k]['sales_amount']));
						$phpexcel->getActiveSheet()->setCellValue('N' . $i, empty($total_sales_amount) ? '0.005' : (sprintf('%.2f', $current_shop[$k]['sales_amount'] / $total_sales_amount * 100) . '%'));
						$phpexcel->getActiveSheet()->setCellValue('O' . $i, sprintf('%.2f', $s['out_rate'] * $r['clearance']));
						unset($current_shop[$k]);
					}
					else
					{
						$phpexcel->getActiveSheet()->setCellValue('J' . $i, $s['shop_name']);
						$phpexcel->getActiveSheet()->setCellValue('K' . $i, 0);
						$phpexcel->getActiveSheet()->setCellValue('L' . $i, '0.00%');
						$phpexcel->getActiveSheet()->setCellValue('M' . $i, 0);
						$phpexcel->getActiveSheet()->setCellValue('N' . $i, '0.00%');
						$phpexcel->getActiveSheet()->setCellValue('O' . $i, sprintf('%.2f', $s['out_rate'] * $r['clearance']));
					}
					$i++;
				}
				foreach ($current_shop as $cs)
				{
					$phpexcel->getActiveSheet()->setCellValue('J' . $i, $cs['shop_name']);
					$phpexcel->getActiveSheet()->setCellValue('K' . $i, sprintf('%.2f', $cs['product_amount']));
					$phpexcel->getActiveSheet()->setCellValue('L' . $i, empty($total_product_amount) ? '0.00%' : (sprintf('%.2f', $cs['product_amount'] / $total_product_amount * 100) . '%'));
					$phpexcel->getActiveSheet()->setCellValue('M' . $i, sprintf('%.2f', $cs['sales_amount']));
					$phpexcel->getActiveSheet()->setCellValue('N' . $i, empty($total_sales_amount) ? '0.00%' : (sprintf('%.2f', $cs['sales_amount'] / $total_sales_amount * 100) . '%'));
					$phpexcel->getActiveSheet()->setCellValue('O' . $i, '0.00');
				}
				$phpexcel->getActiveSheet()->setCellValue('J' . $i, '合计');
				$phpexcel->getActiveSheet()->setCellValue('K' . $i, sprintf('%.2f', $total_product_amount));
				$phpexcel->getActiveSheet()->setCellValue('L' . $i, '');
				$phpexcel->getActiveSheet()->setCellValue('M' . $i, sprintf('%.2f', $total_sales_amount));
				$phpexcel->getActiveSheet()->setCellValue('N' . $i, '');
				$phpexcel->getActiveSheet()->setCellValue('O' . $i, sprintf('%.2f', $r['clearance']));
				$index++;
			}
			//array为以店铺作为第一维度的数组
			$array = array();
			foreach ($result as $key => $r)
			{
				foreach ($r['last_month_sales'] as $rl)
				{
					if ( ! isset($array[$rl['shop_name']]))
					{
						$array[$rl['shop_name']] = array();
					}
				}
			}
			foreach ($result as $key => $r)
			{
				$product_cat = Product_Categories::find('id=?', $key)->setColumns('name')->asArray()->getOne();
				$total_product_amount = 0;
				foreach ($array as $shop_name => $a)
				{
					$array[$shop_name][$product_cat['name']] = 0;
				}
				foreach ($r['last_month_sales'] as $rl)
				{
					$total_product_amount += $rl['product_amount'];
				}
				foreach ($r['last_month_sales'] as $rl)
				{
					$product_rate = sprintf('%.4f', $rl['product_amount'] / $total_product_amount);
					$array[$rl['shop_name']][$product_cat['name']] = sprintf('%.2f', $product_rate * $r['clearance']);
				}
			}
			$first_shop = current($array);
			$phpexcel->createSheet();
			$phpexcel->setActiveSheetIndex($index++);
			$phpexcel->getActiveSheet()->setTitle('汇总');
			$phpexcel->getActiveSheet()->setCellValue('A1', '店铺名');
			$phpexcel->getActiveSheet()->getColumnDimension('A')->setWidth(25);
			$phpexcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$i = 66; //ascii 66=B
			$categories_total = array();
			$total_total = 0;
			foreach ($first_shop as $key => $fs)
			{
				$phpexcel->getActiveSheet()->getColumnDimension(chr($i))->setWidth(25);
				$phpexcel->getActiveSheet()->getStyle(chr($i) . '1')->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				$phpexcel->getActiveSheet()->setCellValue(chr($i++) . '1', $key);
				$categories_total[$key] = 0;
			}
			$phpexcel->getActiveSheet()->getColumnDimension(chr($i))->setWidth(25);
			$phpexcel->getActiveSheet()->getStyle(chr($i) . '1')->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$phpexcel->getActiveSheet()->setCellValue(chr($i++) . '1', '合计');
			$j = 2;
			foreach ($array as $shop_name => $a)
			{
				$i = 65;
				$phpexcel->getActiveSheet()->setCellValue(chr($i++) . $j, $shop_name);
				$shop_total = 0;
				foreach ($first_shop as $key => $fs)
				{
					$phpexcel->getActiveSheet()->setCellValue(chr($i++) . $j, $a[$key]);
					$shop_total += $a[$key];
					$categories_total[$key] += $a[$key];
				}
				$phpexcel->getActiveSheet()->setCellValue(chr($i++) . $j, $shop_total);
				$j++;
			}
			$phpexcel->getActiveSheet()->setCellValue('A' . $j, '合计');
			$i = 66;
			foreach ($first_shop as $key => $fs)
			{
				$phpexcel->getActiveSheet()->setCellValue(chr($i++) . $j, $categories_total[$key]);
				$total_total += $categories_total[$key];
			}
			$phpexcel->getActiveSheet()->setCellValue(chr($i++) . $j, $total_total);
			$outputFileName = '滞销产品信息.xls';
			$objWriter = new PHPExcel_Writer_Excel5($phpexcel);
			header("Content-Type: application/vnd.ms-excel");
			header("Content-Type: application/octet-stream");
			header("Content-Type: applicaiton/download");
			header('Content-Disposition:inine;filename="' . $outputFileName . '"');
			header("Content-Transfer-Encoding: binary");
			header("Pragma: no-cache");
			$objWriter->save('php://output');
			exit;
		}
	}
	
	function actionSalesByShopAndCountry()
	{
		$this->_view['title'] = '销量统计-按店铺国家';
		$this->_view['shop_group'] = Shop_User_Mapping::getTreeShopByPlatform();
		$this->_view['warehouse'] = Warehouse::getActiveWarehouse();
		if (isset($_GET['is_search']) && $_GET['is_search'] == 1 && !empty($_GET['begin_shipped_time']) && !empty($_GET['end_shipped_time']))
		{
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::salesByShopAndCountry($url_arr);
			if ($result['ack'] == SUCCESS)
			{
				$this->_view['report'] = $result['report'];
				$this->_view['url_arr'] = $url_arr;
			}
		}
	}
	
	function actionSalesByShopAndCountryDownload()
	{
		$this->_view['title'] = '销售统计-按店铺国家下载';
		if (isset($_GET['is_search']) && $_GET['is_search'] == 1 && ! empty($_GET['begin_shipped_time']) && ! empty($_GET['end_shipped_time']))
		{
			set_time_limit(0);
			$url_arr = Helper_BSS_Normal::buildCondition();
			$result = OLM_Report::salesByShopAndCountry($url_arr);
			$report = $result['report'];
			include Q::ini('custom_system/lib_dir') . 'PHPExcel.php';
			$phpexcel = new PHPExcel();
			$phpexcel->getProperties()->setTitle('销售统计-按店铺国家清单');
			$phpexcel->getActiveSheetIndex(0);
			$phpexcel->getActiveSheet()->setCellValue('A1', '仓库');
			$phpexcel->getActiveSheet()->setCellValue('B1', '店铺');
			$phpexcel->getActiveSheet()->setCellValue('C1', 'SKU');
			$phpexcel->getActiveSheet()->setCellValue('D1', '产品名称');
			$phpexcel->getActiveSheet()->setCellValue('E1', '国家');
			$phpexcel->getActiveSheet()->setCellValue('F1', '销售数');
			$phpexcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
			$phpexcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
			$phpexcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
			$i = 2;
			if( ! empty($report))
			{
				foreach ($report as $warehouse_name => $rw)
				{
					foreach ($rw['shops'] as $shop_name => $rws)
					{
						foreach ($rws['product_numbers'] as $product_number => $rwsp)
						{
							foreach ($rwsp['countrys'] as $country => $rwspc)
							{
								$phpexcel->getActiveSheet()->getCell('A' . $i)->setValueExplicit($warehouse_name, PHPExcel_cell_DataType::TYPE_STRING);
								$phpexcel->getActiveSheet()->setCellValue('B' . $i, $shop_name);
								$phpexcel->getActiveSheet()->getCell('C' . $i)->setValueExplicit($product_number, PHPExcel_cell_DataType::TYPE_STRING);
								$phpexcel->getActiveSheet()->setCellValue('D' . $i, $rwsp['product_name']);
								$phpexcel->getActiveSheet()->setCellValue('E' . $i, $country);
								$phpexcel->getActiveSheet()->setCellValue('F' . $i, $rwspc['quantity']);
								$i++;
							}
						}
					}
				}
			}
			$output_filename = '销售统计-按店铺国家清单.xls';
			$objwriter = new PHPExcel_Writer_Excel5($phpexcel);
			header("Content-Type: application/vnd.ms-excel");
			header("Content-Type: application/octer-stream");
			header("Content-Type: application/download");
			header('Content-Disposition: inine;filename="' . $output_filename . '"');
			header("Content-Transfer-Encoding: binary");
			header("Pragma: no-cache");
			$objwriter->save('php://output');
			exit;
		}
	}
	
	function actionCountryDistribution()
	{
		$this->_view['title'] = '销量国家分布表';
		$product_categories = Product_Categories::getCategoriesTree();
		$this->_view['product_categories'] = json_encode($product_categories);
		$warehouse = Warehouse::getActiveWarehouse();
		$this->_view['warehouse'] = $warehouse;
		if ( isset($_GET['is_search']) && $_GET['is_search'] = 1 && ! empty($_GET['begin_shipped_time']) && ! empty($_GET['end_shipped_time']))
		{
			if ( ! empty($_GET['product_categories_id']) || ! empty($_GET['sku']))
			{
				$url_arr = Helper_BSS_Normal::buildCondition();
				$result = OLM_Report::CountryDistribution($url_arr);
				$report = Helper_BSS_Array::SortByValue($result['report'], 'quantity', 'desc');
				if ($result['ack'] == SUCCESS)
				{
					$this->_view['report'] = $report;
					$this->_view['url_arr'] = $url_arr;
				}
			}
			else
			{
				return $this->_redirectMessage('查询失败', 'sku和分类至少填写一项！', $_SERVER['HTTP_REFERER'], FAILURE);
			}
		}
	}
	
	function actionMarkOrderReport()
	{
		$this->_view['title'] = '错漏发订单统计表';
		set_time_limit(0);
		$shop = Shop::getActiveShop();
		$this->_view['shop'] = $shop;
		if ( ! empty($_GET['is_search']))
		{
			$condition = Helper_BSS_Normal::buildCondition();
			$where = '1';
		if (! empty($condition['begin_shipped_time']))
		{
			$where .= ' and shipped_time>=\'' . $condition['begin_shipped_time'] . '\'';
		}
		if (! empty($condition['end_shipped_time']))
		{
			$where .= ' and shipped_time<\'' . $condition['end_shipped_time'] . ' 23:59:59\'';
		}
		if (! empty($condition['shop_id']))
		{
			$where .= ' and shop_id= ' . $condition['shop_id'];
		}
		$report = QDB::getConn()->execute("SELECT count(*) as quantity, mark_order_flag FROM `sales_order` where ".$where." and `mark_order_flag` is not null group by `mark_order_flag` ")->fetchAll();
		$this->_view['report'] = $report;
	}
}
}